from django.core.mail import EmailMultiAlternatives
from django.http import JsonResponse
from django.conf import settings
from django.db import connection
from datetime import datetime, timedelta

# 获取今天（T）
today = datetime.today()
t_str = today.strftime("%Y/%m/%d")  # 格式 2025/09/01

# 获取昨天（T-1）
yesterday = today - timedelta(days=1)
t1_str = yesterday.strftime("%Y/%m/%d")

# 构建时间段字符串
time_range_str = f"{t1_str} 17:00 至 {t_str} 17:00"

print(time_range_str)
print("----------------------------------")
# 输出示例: "2025/08/31 17:00 至 2025/09/01 17:00"




# --------------------------
# 通用函数：生成 HTML 表格
# --------------------------
def generate_html_table(data_list, headers_map):
    """
    data_list: [{'col1': val1, 'col2': val2, ...}, ...]
    headers_map: {'col1': '中文列名', 'col2': '中文列名', ...}
    """
    html = '<table border="1" cellpadding="6" cellspacing="0" ' \
           'style="border-collapse: collapse; width: auto; table-layout: fixed; font-family: Arial, sans-serif;">'
    # 表头
    html += '<tr style="background-color: #4682B4; color: white; text-align: center; font-weight: bold;">'
    for key in headers_map:
        html += f'<th>{headers_map[key]}</th>'
    html += '</tr>'

    # 表体
    for i, row in enumerate(data_list):
        bg_color = "#e6f7ff" if i % 2 else "#ffffff"
        html += f'<tr style="background-color: {bg_color}; text-align: center;">'
        for key in headers_map:
            html += f'<td>{row.get(key, "")}</td>'
        html += '</tr>'
    html += '</table>'
    return html


# --------------------------
# 通用函数：执行 SQL 并返回字典列表
# --------------------------
def fetch_sql_dict(sql, params=None):
    with connection.cursor() as cursor:
        cursor.execute(sql, params or [])
        columns = [col[0] for col in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]


# --------------------------
# 各报表生成函数
# --------------------------
def get_daily_policy_html():
    reports = []

    # 个险电子
    sql1 = "SELECT date, policy_type, policy_count, abnormal_count, success_rate FROM policy_summary WHERE policy_type='个险电子' AND date=%s"
    data1 = fetch_sql_dict(sql1, ['2025-09-01'])
    if data1:
        headers1 = {'date': '日期', 'policy_type': '保单类型', 'policy_count': '保单本数',
                'abnormal_count': '异常笔数', 'success_rate': '成功率'}
    reports.append(generate_html_table(data1, headers1))

    # 个险纸质
    sql2 = "SELECT date, policy_type, policy_count, abnormal_count, success_rate, shida, lirenhang, donggang FROM policy_summary WHERE policy_type='个险纸质' AND date=%s"
    data2 = fetch_sql_dict(sql2, ['2025-09-01'])
    headers2 = {'date': '日期', 'policy_type': '保单类型', 'policy_count': '保单本数',
                'abnormal_count': '异常笔数', 'success_rate': '成功率',
                'shida': '实达', 'lirenhang': '立人行', 'donggang': '东港'}
    reports.append(generate_html_table(data2, headers2))

    # 新核心单证
    sql3 = "SELECT date, policy_type, policy_count, abnormal_count, success_rate FROM policy_summary WHERE policy_type='新核心单证' AND date=%s"
    data3 = fetch_sql_dict(sql3, ['2025-09-01'])
    headers3 = headers1
    reports.append(generate_html_table(data3, headers3))

    # 团险电子
    sql4 = "SELECT date, policy_type, policy_count, abnormal_count, success_rate FROM policy_summary WHERE policy_type='团险电子' AND date=%s"
    data4 = fetch_sql_dict(sql4, ['2025-09-01'])
    reports.append(generate_html_table(data4, headers1))

    # 团险纸质
    sql5 = "SELECT date, policy_type, policy_count, abnormal_count, success_rate, shida, lirenhang, donggang FROM policy_summary WHERE policy_type='团险纸质' AND date=%s"
    data5 = fetch_sql_dict(sql5, ['2025-09-01'])
    reports.append(generate_html_table(data5, headers2))

    return "<p></p>".join(reports)


def get_disk_html():
    sql = "SELECT server_ip as 服务器, free_size as 'D盘剩余空间', disk_total as 'D盘总容量', free_rate as 'D盘空余比列' FROM disk_monitor"
    data = fetch_sql_dict(sql)
    if data:
        headers = list(data[0].keys())
    return generate_html_table(data, {k: k for k in headers })


def get_db_space_html():
    sql = "SELECT tablename, tabel_size , table_free, table_use , tabel_size_g , table_free_g, table_use_g , free_rate FROM database_space"
    data = fetch_sql_dict(sql)
    if not data:
        return "<p>暂无数据库空间数据</p>"
    #headers = list(data[0].keys())

    """
    data_list: [{'col1': val1, 'col2': val2, ...}, ...]
    headers_map: {'col1': '中文列名', 'col2': '中文列名', ...}
    """
    html = '<table border="1" cellpadding="6" cellspacing="0" style="border-collapse: collapse; width: auto; table-layout: fixed; font-family: Arial, sans-serif; white-space: nowrap; overflow: hidden; text-overflow: ellipsis;">'

    html +=  '<tr style="background-color: #f2f2f2">' \
         '<th style="width: 100px;">表名空间名</th>' \
         '<th style="width: 120px;">表空间大小</th>' \
         '<th style="width: 120px;">表空间剩余大小</th>' \
         '<th style="width: 120px;">表空间使用大小</th>' \
         '<th style="width: 130px;">表空间大小(G)</th>' \
         '<th style="width: 140px;">表空间剩余大小(G)</th>' \
         '<th style="width: 140px;">表空间使用大小(G)</th>' \
         '<th style="width: 80px;">使用率</th>' \
         '</tr>'
    print(f"data is {data}")
    for row in data:
        html += f'''
        <tr>
            <td style="text-align: center; vertical-align: middle; padding: 10px;">{row.get("tablename","")}</td>
            <td style="text-align: center; vertical-align: middle; padding: 10px;">{row.get("tabel_size","")}</td>
            <td style="text-align: center; vertical-align: middle; padding: 10px;">{row.get("table_free","")}</td>
            <td style="text-align: center; vertical-align: middle; padding: 10px;">{row.get("table_use","")}</td>
            <td style="text-align: center; vertical-align: middle; padding: 10px;">{row.get("tabel_size_g","")}</td>
            <td style="text-align: center; vertical-align: middle; padding: 10px;">{row.get("table_free_g","")}</td>
            <td style="text-align: center; vertical-align: middle; padding: 10px;">{row.get("table_use_g","")}</td>
            <td style="text-align: center; vertical-align: middle; padding: 10px;">{row.get("free_rate","")}</td>
        </tr>
        '''
    html += '</table>'
    return html


# --------------------------
# 发送邮件
# --------------------------
def send_email_with_table(request):
    try:
        html_content =f'''
        <html>
        <head>
            <meta charset="UTF-8">
            <style>
                body {{
                    margin: 0;
                    padding: 20px;
                    font-family: "Arial", sans-serif;
                    color: #333;
                    line-height: 1.6;
                }}
                table {{
                    border-collapse: collapse;
                    width: 100%;
                    table-layout: fixed; /* 固定列宽 */
                    text-align: center;
                    margin-bottom: 20px; /* 表格间距 */
                }}
                th, td {{
                    border: 1px solid #0077b6;
                    padding: 8px;
                    width: 100px; /* 固定单元格宽度，保证对齐 */
                    white-space: nowrap; /* 防止文字换行 */
                    overflow: hidden; /* 隐藏溢出内容 */
                    text-overflow: ellipsis; /* 用省略号表示溢出 */
                }}

            </style>
        </head>
        <body>
            <h3>尊敬的各位领导、同事：</h3>
            <h4 style="text-indent: 2em; margin: 0; font-size: 16px;" >大家好！</h4>
            <h4 style="text-indent: 2em; margin: 0; font-size: 16px;">打印平台今日服务器运行正常，并保持稳定运行。{time_range_str}，共处理交易xxx笔，其中成功 xxx笔，失败 0笔。</h4>
            <p></p>
            <div>
                <h4 style="text-indent: 2em; margin: 0; font-size: 16px;">1、日常数据统计</h4>
                <div style="margin-left: 2em;">
                {get_daily_policy_html()}
                <p></p>
                </div>
                <h4 style="text-indent: 2em; margin: 0; font-size: 16px;">2、硬盘空间监控</h4>
                <div style="margin-left: 2em;">
                {get_disk_html()} 
                </div>
                <h4 style="text-indent: 2em; margin: 0; font-size: 16px;">3、数据库空间监控</h4>
                <div style="margin-left: 2em;">
                {get_db_space_html()}
                </div>
            </div>
        </body>
        </html>
        '''

        msg = EmailMultiAlternatives(
            subject='打印平台运行日报',
            from_email=settings.DEFAULT_FROM_EMAIL,
            to=['13367812895@163.com']
        )
        msg.attach_alternative(html_content, "text/html")
        msg.send()
        return JsonResponse({'code': 200, 'msg': '发送成功'})
    except Exception as e:
        print(f"Exception: {e}")
        return JsonResponse({'code': 500, 'msg': '邮件发送失败'})
